﻿using DeviceManager.DB.Model;
using Sails.DB;
using Sails.DB.Helper;
using Sails.FileMng.Ini;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading;

namespace DeviceManager.DB
{
    /// <summary>
    /// 数据库管理器
    /// </summary>
    class ManagerDBCtrl:IDisposable
    {
        private static ManagerDBCtrl dbCtrl = null;
        public static ManagerDBCtrl GetMng()
        {
            if (dbCtrl == null)
            {
                dbCtrl = new ManagerDBCtrl();
            }
            return dbCtrl;
        }

        private DataBase dataBase = null;

        private Thread LoopThread = null;

        public ManagerDBCtrl()
        {
            Sails.DB.DBConnectInfo connect = new DBConnectInfo();
            INIFile config = new INIFile(AppDomain.CurrentDomain.BaseDirectory + "config/dbconn.ini");
            connect.DBHost = config.IniReadValue("db", "host");
            connect.DBPort = config.IniReadValue("db", "port");
            connect.DBName = config.IniReadValue("db", "dbname");
            connect.DBUserName = config.IniReadValue("db", "username");
            connect.DBUserPwd = config.IniReadValue("db", "userpwd");
            dataBase = DataBase.GetDataBase(connect);
            if (dataBase==null || !dataBase.TestConnect())
            {
                dataBase = null;
                throw new Exception("初始化数据库失败，请检查数据库配置!");
            }

            LoopThread = new Thread(new ThreadStart(delegate ()
            {
                while (true)
                {
                    LoopMakeHomeData();
                    //半小时更新一次
                    Thread.Sleep(60 * 1000 * 30);
                }
            }));
            LoopThread.Start();
        }


        /// <summary>
        /// 获取最大值
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="failedName"></param>
        /// <returns></returns>
        private int GetMaxID(string tableName, string failedName)
        {
            string sql = string.Format("select ISNULL(MAX({1}),0)+1 id from {0}", tableName, failedName);
            DataTable dt = dataBase.Exec(sql, null);
            if (dt != null)
            {
                if (dt.Rows.Count > 0)
                {
                    return DBTools.GetInt(dt.Rows[0], "id");
                }
            }
            return -1;
        }


        /// <summary>
        /// 用户登录
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="userPwd"></param>
        /// <returns></returns>
        public User UserLogin(string userName,string userPwd)
        {
            string sql = "select * from Users where loginname=@loginname and loginpwd = @loginpwd";
            DataTable dt = dataBase.Exec(sql, new Dictionary<string, object>() { { "loginname", userName }, { "loginpwd", userPwd } });
            if (dt != null && dt.Rows.Count > 0)
            {
                User user = new User();
                user.id = DBTools.GetInt(dt.Rows[0], "id");
                user.loginName = DBTools.GetString(dt.Rows[0], "loginName");
                user.lginPwd = DBTools.GetString(dt.Rows[0], "loginPwd");
                user.nickName = DBTools.GetString(dt.Rows[0], "nickName");
                user.depID = DBTools.GetInt(dt.Rows[0], "depid");
                user.userType = DBTools.GetInt(dt.Rows[0], "usertype");
                return user;
            }
            return null;
        }

        
        /// <summary>
        /// 根据行数据解析出结构对象
        /// </summary>
        /// <returns></returns>
        private DepmentInfo PaseraDataRowToDepMentInfo(DataRow dr)
        {
            DepmentInfo dep = new DepmentInfo();
            dep.Id = DBTools.GetInt(dr, "id");
            dep.Name = DBTools.GetString(dr, "name");
            dep.Des = DBTools.GetString(dr, "des");
            dep.pid = DBTools.GetInt(dr, "pid");
            return dep;
        }

        /// <summary>
        /// 递归读取组织机构信息集合
        /// </summary>
        /// <param name="depInfo"></param>
        public void CallGetDepmentChiledInfo(DepmentInfo depInfo)
        {
            if (depInfo.Id == 0)
            {
                return;
            }
            string sql = "select * from Dep where pid = " + depInfo.Id;
            DataTable dt = dataBase.Exec(sql, null);
            if (dt != null)
            {
                foreach(DataRow dr in dt.Rows)
                {
                    DepmentInfo dep = PaseraDataRowToDepMentInfo(dr);
                    if (depInfo.Chileds == null)
                    {
                        depInfo.Chileds = new List<DepmentInfo>();
                    }
                    //递归读取信息
                    CallGetDepmentChiledInfo(dep);
                    depInfo.Chileds.Add(dep);                   
                }
            }
        }


        /// <summary>
        /// 读取所有部门信息
        /// </summary>
        /// <returns></returns>
        public List<DepmentInfo> GetAllDepmentInfos()
        {
            string sql = "select * from Dep where pid is null";
            DataTable dt = dataBase.Exec(sql,null);
            if (dt != null)
            {
                List<DepmentInfo> depmentinfos = new List<DepmentInfo>();
                foreach(DataRow de in dt.Rows)
                {
                    DepmentInfo dep = PaseraDataRowToDepMentInfo(de);
                    CallGetDepmentChiledInfo(dep);
                    depmentinfos.Add(dep);
                }
                return depmentinfos;
            }
            return null;
        }

        /// <summary>
        /// 保存组织机构信息
        /// </summary>
        /// <param name="dep"></param>
        /// <returns></returns>
        public bool SaveDepmentInfo(DepmentInfo dep)
        {
            if (dep == null)
            {
                return false;
            }
            if (dep.pid < 1)
            {
                return false;
            }
            if (dep.Id == 0)
            {
                string sql = "INSERT INTO [Dep]([Id],[Name],[DES],[pid]) VALUES(@Id,@Name,@DES,@pid)";
                dep.Id = GetMaxID("Dep", "id");
                return dataBase.IExec(sql, new Dictionary<string, object>()
                {
                    {"id",dep.Id },
                    {"name",dep.Name },
                    {"DES",dep.Des },
                    {"pid",dep.pid }
                }) > 0;
            }
            else
            {
                string sql = "update Dep set Name=@name,DES = @des,pid=@pid where id=@id";
                //dep.Id = GetMaxID("Dep", "id");
                return dataBase.IExec(sql, new Dictionary<string, object>()
                {
                    {"id",dep.Id },
                    {"name",dep.Name },
                    {"DES",dep.Des },
                    {"pid",dep.pid }
                }) > 0;
            }
        }

        /// <summary>
        /// 移除部门信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DeleteDepMenteInfo(int id)
        {
            string sql = "delete Dep where id =" + id;
            return dataBase.IExec(sql, null) > 0;
        }
        /// <summary>
        /// 根据ID获取部门信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DepmentInfo GetDepmentInfoByID(int id)
        {
            string sql = "select * from dep where id = " + id;
            DataTable dt = dataBase.Exec(sql, null);
            if(dt!=null && dt.Rows.Count > 0)
            {
                return PaseraDataRowToDepMentInfo(dt.Rows[0]);
            }
            return null;
        }

        /// <summary>
        /// 将行内容解析为用户信息
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        internal User PaseraDataRowToUser(DataRow dr)
        {
            User user = new User();
            user.id = DBTools.GetInt(dr, "id");
            user.loginName = DBTools.GetString(dr, "loginname");
            user.nickName = DBTools.GetString(dr, "nickname");
            user.userType = DBTools.GetInt(dr, "usertype");
            user.lginPwd = DBTools.GetString(dr, "loginPwd");
            user.depID = DBTools.GetInt(dr, "depid");
            return user;
        }
        /// <summary>
        /// 根据ID获取用户信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        internal User GetUserByID(int id)
        {
            string sql = "select * from users where id = " + id;
            DataTable dt = dataBase.Exec(sql, null);
            if (dt != null)
            {
                if (dt.Rows.Count > 0)
                {
                    return PaseraDataRowToUser(dt.Rows[0]);
                }
            }
            return null;
        }

        /// <summary>
        /// 根据用户姓名模糊查询数据，如果为空，那么则查询全部信息
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        internal List<User> GetUserInfo(string username)
        {
            List<User> users = new List<User>();
            string where = username.Replace("-", "").Replace("%", "").Replace("or","").Replace("and", "").Replace("select", "").Replace("exec", "").Replace("insert", "").Replace("update", "").Replace("union", "").Replace("\"", "");
            string sql = "select* from Users where loginName like '%'" + where + "'%' or nickName like '%" + where + "%'";
            DataTable dt = dataBase.Exec(sql, null);
            if (dt != null)
            {
                foreach(DataRow dr in dt.Rows)
                {
                    users.Add(PaseraDataRowToUser(dr));
                }
            }
            return users;
        }
        /// <summary>
        /// 删除用户信息
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        internal bool DelUserInfo(int userID)
        {
            string sql = "delete users where id = " + userID;
            return dataBase.IExec(sql, null)>0;
        }

        /// <summary>
        /// 保存用户信息
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        internal bool SaveUserInfo(User user)
        {
            if (user.id == 0)
            {
                user.id = GetMaxID("users","id");
                string sql = "INSERT INTO [Users]([Id],[loginName],[loginPwd],[depID],[userType],[nickName]) VALUES (@Id,@loginName,@loginPwd,@depID,@userType,@nickName)";
                return dataBase.IExec(sql, new Dictionary<string, object>()
                {
                    {"Id",user.id },
                    {"loginName",user.loginName },
                    {"loginPwd",user.lginPwd },
                    {"depID",user.depID },
                    {"userType",user.userType },
                    {"nickName",user.nickName }
                }) > 0;
            }
            else
            {
                string sql = "UPDATE [Users] SET [loginName] = @loginName,[loginPwd] = @loginPwd,[depID] = @depID,[userType] = @userType,[nickName] = @nickName WHERE [Id] = @Id";
                return dataBase.IExec(sql, new Dictionary<string, object>()
                {
                    {"Id",user.id },
                    {"loginName",user.loginName },
                    {"loginPwd",user.lginPwd },
                    {"depID",user.depID },
                    {"userType",user.userType },
                    {"nickName",user.nickName }
                }) > 0;
            }
        }

        /// <summary>
        /// 解析数据行解析为设备数据
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        private DeviceNode PaseraDataRowToDeviceNode(DataRow dr)
        {
            DeviceNode deviceNode = new DeviceNode();
            deviceNode.Id = DBTools.GetInt(dr, "id");
            deviceNode.DeviceCode = DBTools.GetString(dr, "DeviceCode");
            deviceNode.DeviceType = DBTools.GetInt(dr, "DeviceType");
            deviceNode.DeviceDepID = DBTools.GetInt(dr, "DeviceDepID");
            deviceNode.DeviceName = DBTools.GetString(dr, "DeviceName");
            deviceNode.DeviceDepName = DBTools.GetString(dr, "DepName");
            deviceNode.DeviceTypeName = DBTools.GetString(dr, "typeName");
            deviceNode.DeviceDes = DBTools.GetString(dr, "DeviceDes");
            deviceNode.DeviceInstall = DBTools.GetString(dr, "DeviceInstall");
            deviceNode.DeviceMaxVoltage = DBTools.GetFloat(dr, "DeviceMaxVoltage");
            deviceNode.DeviceMinVoltage = DBTools.GetFloat(dr, "DeviceMinVoltage");
            deviceNode.DeviceVoltage = DBTools.GetFloat(dr, "DeviceVoltage");
            deviceNode.DeviceMaxElectricCurrent = DBTools.GetFloat(dr, "DeviceMaxElectricCurrent");
            deviceNode.DeviceMinElectricCurrent = DBTools.GetFloat(dr, "DeviceMinElectricCurrent");
            deviceNode.DeviceElectricCurrent = DBTools.GetFloat(dr, "DeviceElectricCurrent");            
            deviceNode.DeviceMaxTemperature = DBTools.GetFloat(dr, "DeviceMaxTemperature");
            deviceNode.DeviceMinTemperature = DBTools.GetFloat(dr, "DeviceMinTemperature");
            deviceNode.DeviceTemperature = DBTools.GetFloat(dr, "DeviceTemperature");
            deviceNode.DeviceMaxHumidity = DBTools.GetFloat(dr, "DeviceMaxHumidity");
            deviceNode.DeviceMinHumidity = DBTools.GetFloat(dr, "DeviceMinHumidity");
            deviceNode.DeviceHumidity = DBTools.GetFloat(dr, "DeviceHumidity");
            deviceNode.DeviceIsMaintain = DBTools.GetInt(dr, "DeviceIsMaintain");
            deviceNode.DeviceLastUpDate = DBTools.GetDateTime(dr, "DeviceLastUpDate").Value;
            deviceNode.DeviceUpDateTime = DBTools.GetFloat(dr, "DeviceUpDateTime");
            deviceNode.installDate = DBTools.GetDateTime(dr, "DeviceAddDateTime").Value;
            deviceNode.DeviceIsOnLine = (DateTime.Now - deviceNode.DeviceLastUpDate).TotalMinutes < deviceNode.DeviceUpDateTime;
            return deviceNode;
        }

        /// <summary>
        /// 根据查询条件查询设备信息
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        internal List<DeviceNode> GetAllDevices(string query)
        {
            string sql = "select a.*,b.Name depName,c.Name typeName from [Devices] a inner join dep b on a.deviceDepID = b.Id inner join DeviceType c on a.DeviceType =c.Id " + (string.IsNullOrEmpty(query) ? "" : "  and " + query);
            DataTable dt = dataBase.Exec(sql, null);
            List<DeviceNode> deviceNodes = new List<DeviceNode>();
            if (dt != null)
            {
                foreach(DataRow dr in dt.Rows)
                {
                    deviceNodes.Add(PaseraDataRowToDeviceNode(dr));
                }
            }
            return deviceNodes;
        }


        /// <summary>
        /// 根据设备类型名称获取设备类型ID
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        internal int GetDeviceTypeIDByName(string name)
        {
            string sql = "select * from DeviceType where Name = @name";
            DataTable dt= dataBase.Exec(sql, new Dictionary<string, object>() { { "name", name } });
            if(dt!=null && dt.Rows.Count > 0)
            {
                return DBTools.GetInt(dt.Rows[0], "id");
            }
            else
            {
                return -1;
            }
        }

        /// <summary>
        /// 根据名称获取部门信息
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        internal int GetDepIDByName(string name)
        {
            string sql = "select * from Dep where Name = @name";
            DataTable dt = dataBase.Exec(sql, new Dictionary<string, object>() { { "name", name } });
            if (dt != null && dt.Rows.Count > 0)
            {
                return DBTools.GetInt(dt.Rows[0], "id");
            }
            else
            {
                return -1;
            }
        }

        /// <summary>
        /// 保存设备信息
        /// </summary>
        /// <param name="deviceNode"></param>
        /// <returns></returns>
        internal bool SaveDeviceNode(DeviceNode deviceNode)
        {
            if (deviceNode.Id == 0)
            {
                deviceNode.Id = GetMaxID("Devices", "id");
                string sql = @"INSERT INTO[Devices]([Id],[DeviceCode],[DeviceType],[DeviceDepID],[DeviceName],[DeviceDes],[DeviceInstall],[DeviceMaxVoltage],[DeviceMinVoltage],[DeviceVoltage],[DeviceMaxElectricCurrent]
                               ,[DeviceMinElectricCurrent],[DeviceElectricCurrent],[DeviceMaxTemperature],[DeviceMinTemperature],[DeviceTemperature],[DeviceMaxHumidity],[DeviceMinHumidity]
                               ,[DeviceHumidity],[DeviceIsMaintain],[DeviceLastUpDate],[DeviceUpDateTime],[DeviceAddDateTime]) VALUES
                               (@Id, @DeviceCode, @DeviceType,@DeviceDepID, @DeviceName, @DeviceDes, @DeviceInstall, @DeviceMaxVoltage, @DeviceMinVoltage, @DeviceVoltage, @DeviceMaxElectricCurrent,
                               @DeviceMinElectricCurrent, @DeviceElectricCurrent, @DeviceMaxTemperature, @DeviceMinTemperature, @DeviceTemperature, @DeviceMaxHumidity, @DeviceMinHumidity,
                               @DeviceHumidity, @DeviceIsMaintain, @DeviceLastUpDate, @DeviceUpDateTime, @DeviceAddDateTime)";
                if(dataBase.IExec(sql, new Dictionary<string, object>()
                {
                    {"Id",deviceNode.Id },
                    {"DeviceCode",deviceNode.DeviceCode },
                    {"DeviceType",deviceNode.DeviceType },
                    {"DeviceDepID",deviceNode.DeviceDepID },
                    {"DeviceName",deviceNode.DeviceName },
                    {"DeviceDes",deviceNode.DeviceDes },
                    {"DeviceInstall",deviceNode.DeviceInstall },
                    {"DeviceMaxVoltage",deviceNode.DeviceMaxVoltage },
                    {"DeviceMinVoltage",deviceNode.DeviceMinVoltage },
                    {"DeviceVoltage",deviceNode.DeviceVoltage },
                    {"DeviceMaxElectricCurrent",deviceNode.DeviceMaxElectricCurrent },
                    {"DeviceMinElectricCurrent",deviceNode.DeviceMinElectricCurrent },
                    {"DeviceElectricCurrent",deviceNode.DeviceElectricCurrent },
                    {"DeviceMaxTemperature",deviceNode.DeviceMaxTemperature },
                    {"DeviceMinTemperature",deviceNode.DeviceMinTemperature },
                    {"DeviceTemperature",deviceNode.DeviceTemperature },
                    {"DeviceMaxHumidity",deviceNode.DeviceMaxHumidity },
                    {"DeviceMinHumidity",deviceNode.DeviceMinHumidity },
                    {"DeviceHumidity",deviceNode.DeviceHumidity },
                    {"DeviceIsMaintain",deviceNode.DeviceIsMaintain },
                    {"DeviceLastUpDate",DateTime.Now },
                    {"DeviceUpDateTime",deviceNode.DeviceUpDateTime },
                    {"DeviceAddDateTime",deviceNode.installDate }
                }) > 0)
                {
                    //更新新增数
                    //先查询本日数据是否已存在

                    sql = "select * from HomePageData where convert(varchar(10),SumDate,120)  = convert(varchar(10),GETDATE(),120) and SumType = 0";
                    DataTable dt = dataBase.Exec(sql, null);
                    if(dt!=null && dt.Rows.Count > 0)
                    {
                        //已存在，更新它
                        sql = "update HomePageData set SumValue=SumValue+1 where convert(varchar(10),SumDate,120)  = convert(varchar(10),GETDATE(),120) and SumType = 0";
                        dataBase.IExec(sql, null);
                    }
                    else
                    {
                        //插入数据
                        sql = "insert into homePageData values(" + GetMaxID("HomePageData", "id") + ",GETDATE(),0,1)";
                        dataBase.IExec(sql, null);
                    }
                    return true;
                }
                else
                {
                    return false;
                }
            }
            else
            {
                string sql = @"UPDATE [Devices] SET [DeviceCode] = @DeviceCode,[DeviceType] = @DeviceType,[DeviceName] = @DeviceName,[DeviceDes] = @DeviceDes,[DeviceInstall] = @DeviceInstall,[DeviceMaxVoltage] = @DeviceMaxVoltage
                              ,[DeviceMinVoltage] = @DeviceMinVoltage,[DeviceMaxElectricCurrent] = @DeviceMaxElectricCurrent,[DeviceMinElectricCurrent] = @DeviceMinElectricCurrent
                              ,[DeviceMaxTemperature] = @DeviceMaxTemperature,[DeviceMinTemperature] = @DeviceMinTemperature
                              ,[DeviceMaxHumidity] = @DeviceMaxHumidity,[DeviceMinHumidity] = @DeviceMinHumidity,[DeviceIsMaintain] = @DeviceIsMaintain
                              ,[DeviceUpDateTime] = @DeviceUpDateTime,DeviceDepID=@DeviceDepID,DeviceLastUpDate=@DeviceLastUpDate WHERE [Id] = @Id";

                return dataBase.IExec(sql, new Dictionary<string, object>()
                {
                    {"Id",deviceNode.Id },
                    {"DeviceCode",deviceNode.DeviceCode },
                    {"DeviceType",deviceNode.DeviceType },
                    {"DeviceDepID",deviceNode.DeviceDepID },
                    {"DeviceName",deviceNode.DeviceName },
                    {"DeviceDes",deviceNode.DeviceDes },
                    {"DeviceInstall",deviceNode.DeviceInstall },
                    {"DeviceMaxVoltage",deviceNode.DeviceMaxVoltage },
                    {"DeviceMinVoltage",deviceNode.DeviceMinVoltage },
                    //{"DeviceVoltage",deviceNode.DeviceVoltage },
                    {"DeviceMaxElectricCurrent",deviceNode.DeviceMaxElectricCurrent },
                    {"DeviceMinElectricCurrent",deviceNode.DeviceMinElectricCurrent },
                    //{"DeviceElectricCurrent",deviceNode.DeviceElectricCurrent },
                    {"DeviceMaxTemperature",deviceNode.DeviceMaxTemperature },
                    {"DeviceMinTemperature",deviceNode.DeviceMinTemperature },
                    //{"DeviceTemperature",deviceNode.DeviceTemperature },
                    {"DeviceMaxHumidity",deviceNode.DeviceMaxHumidity },
                    {"DeviceMinHumidity",deviceNode.DeviceMinHumidity },
                    //{"DeviceHumidity",deviceNode.DeviceHumidity },
                    {"DeviceIsMaintain",deviceNode.DeviceIsMaintain },
                    {"DeviceLastUpDate",deviceNode.DeviceLastUpDate },
                    {"DeviceUpDateTime",deviceNode.DeviceUpDateTime }
                }) > 0;
            }
        }



        public bool UpDateDeviceStatus(DeviceNode deviceNode)
        {
            string sql = "update Devices set DeviceVoltage = @DeviceVoltage,DeviceElectricCurrent = @DeviceElectricCurrent,DeviceTemperature = @DeviceTemperature,DeviceHumidity=@DeviceHumidity,DeviceLastUpDate=@DeviceLastUpDate where id = @id";
            return dataBase.IExec(sql, new Dictionary<string, object>()
                {
                    {"Id",deviceNode.Id },
                    {"DeviceVoltage",deviceNode.DeviceVoltage },
                    {"DeviceElectricCurrent",deviceNode.DeviceElectricCurrent },
                    {"DeviceTemperature",deviceNode.DeviceTemperature },
                    {"DeviceHumidity",deviceNode.DeviceHumidity },
                    {"DeviceLastUpDate",deviceNode.DeviceLastUpDate }
                }) > 0;
        }

        /// <summary>
        /// 移除设备
        /// </summary>
        /// <param name="deviceID"></param>
        /// <returns></returns>
        internal bool DelDeviceNode(int deviceID)
        {
            string sql = "delete Devices where id =" + deviceID;
            if(dataBase.IExec(sql, null) > 0)
            {
                sql = "select * from HomePageData where convert(varchar(10),SumDate,120)  = convert(varchar(10),GETDATE(),120) and SumType = 1";
                DataTable dt = dataBase.Exec(sql, null);
                if (dt != null && dt.Rows.Count > 0)
                {
                    //已存在，更新它
                    sql = "update HomePageData set SumValue=SumValue+1 where convert(varchar(10),SumDate,120)  = convert(varchar(10),GETDATE(),120) and SumType = 1";
                    dataBase.IExec(sql, null);
                }
                else
                {
                    //插入数据
                    sql = "insert into homePageData values(" + GetMaxID("HomePageData", "id") + ",GETDATE(),1,1)";
                    dataBase.IExec(sql, null);
                }
                return true;
            }
            return false;
        }


        /// <summary>
        /// 获取在线设备数
        /// </summary>
        /// <returns></returns>
        internal int GetOnLineDeviceCount()
        {
            string sql = "select COUNT(*) onLineCount from Devices where datediff(MINUTE,DeviceLastUpDate,GETDATE())<=DeviceUpDateTime";
            DataTable dt = dataBase.Exec(sql, null);
            if(dt!=null && dt.Rows.Count > 0)
            {
                return DBTools.GetInt(dt.Rows[0], "onLineCount");
            }
            return 0;
        }

        /// <summary>
        /// 获取离线设备数 不含维护设备
        /// </summary>
        /// <returns></returns>
        internal int GetOffLineDeviceCount()
        {
            string sql = "select  COUNT(*) offLineCount from Devices where datediff(MINUTE,DeviceLastUpDate,GETDATE())>DeviceUpDateTime  and DeviceIsMaintain = 0";
            DataTable dt = dataBase.Exec(sql, null);
            if (dt != null && dt.Rows.Count > 0)
            {
                return DBTools.GetInt(dt.Rows[0], "offLineCount");
            }
            return 0;
        }

        /// <summary>
        /// 获取维护设备数
        /// </summary>
        /// <returns></returns>
        internal int GetMaintainDeviceCount()
        {
            string sql = "select COUNT(*) mainCount from Devices where DeviceIsMaintain = 1";
            DataTable dt = dataBase.Exec(sql, null);
            if (dt != null && dt.Rows.Count > 0)
            {
                return DBTools.GetInt(dt.Rows[0], "mainCount");
            }
            return 0;
        }


        /// <summary>
        /// 获取告警设备数
        /// </summary>
        /// <returns></returns>
        internal int GetWarrDeviceCount()
        {
            string sql = @"select COUNT(*) mainCount from Devices where
not(DeviceVoltage between DeviceMinVoltage and DeviceMaxVoltage
or DeviceElectricCurrent between DeviceMinElectricCurrent and DeviceMaxElectricCurrent
or DeviceTemperature between DeviceMinTemperature and DeviceMaxTemperature
or DeviceHumidity between DeviceMinHumidity and DeviceMaxHumidity)
and datediff(MINUTE, DeviceLastUpDate, GETDATE()) <= DeviceUpDateTime
and DeviceIsMaintain = 0";
            DataTable dt = dataBase.Exec(sql, null);
            if (dt != null && dt.Rows.Count > 0)
            {
                return DBTools.GetInt(dt.Rows[0], "mainCount");
            }
            return 0;
        }

        /// <summary>
        /// 获取所有数倍数量
        /// </summary>
        /// <returns></returns>
        internal int GetDeviceCount()
        {
            string sql = "select count(*) dcount from devices";
            DataTable dt = dataBase.Exec(sql, null);
            if (dt != null && dt.Rows.Count > 0)
            {
                return DBTools.GetInt(dt.Rows[0], "dcount");
            }
            return 0;
        }

        /// <summary>
        /// 获取当前近一周的统计数据
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        internal List<StatisticsDataNode> GetStatisticsDateNodes(SumDataType type)
        {
            List<StatisticsDataNode> dataNodes = new List<StatisticsDataNode>();
            string sql = "select * from HomePageData where SumType = "+(int)type+ " and SumDate between DATEADD(day,-7,GETDATE()) and GETDATE()  order by SumDate";
            DataTable dt = dataBase.Exec(sql, null);
            if (dt != null)
            {
                foreach(DataRow dr in dt.Rows)
                {
                    StatisticsDataNode node = new StatisticsDataNode();
                    node.sumDate = DBTools.GetDateTime(dr, "SumDate").Value;
                    node.Value = DBTools.GetInt(dr, "SumValue");
                    dataNodes.Add(node);
                }
            }
            return dataNodes;            
        }


        internal DeviceTypeInfo PaseraDataRowToDeviceTypeInfo(DataRow dr)
        {
            DeviceTypeInfo info = new DeviceTypeInfo();
            info.Id = DBTools.GetInt(dr, "id");
            info.Name = DBTools.GetString(dr, "Name");
            info.Des = DBTools.GetString(dr, "Des");
            info.Pid = DBTools.GetInt(dr, "Pid");
            return info;
        }

        internal void CallBackGetDeviceTypeInfos(DeviceTypeInfo info)
        {
            string sql = "select * from devicetype where pid =" + info.Id;
            DataTable dt = dataBase.Exec(sql, null);
            if (dt != null)
            {
                foreach(DataRow dr in dt.Rows)
                {
                    DeviceTypeInfo dev = PaseraDataRowToDeviceTypeInfo(dr);
                    CallBackGetDeviceTypeInfos(dev);
                    if (info.Chileds == null)
                    {
                        info.Chileds = new List<DeviceTypeInfo>();
                    }
                    info.Chileds.Add(dev);
                }
            }
        }

        /// <summary>
        /// 获取所有设备类型信息
        /// </summary>
        /// <returns></returns>
        internal List<DeviceTypeInfo> GetAllDeviceTypeInfo()
        {
            string sql = "select * from DeviceType where Pid = 0";
            List<DeviceTypeInfo> deviceTypeinfos = new List<DeviceTypeInfo>();
            DataTable dt = dataBase.Exec(sql, null);
            if (dt != null)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    DeviceTypeInfo dev = PaseraDataRowToDeviceTypeInfo(dr);
                    CallBackGetDeviceTypeInfos(dev);
                    deviceTypeinfos.Add(dev);
                }
            }
            return deviceTypeinfos;
        }

        /// <summary>
        /// 保存设备类型信息
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        internal bool SaveDeviceTypeInfo(DeviceTypeInfo info)
        {
            if (info.Id == 0)
            {
                string sql = "insert into deviceType values(@id,@name,@des,@pid)";
                info.Id = GetMaxID("deviceType", "id");
                return dataBase.IExec(sql, new Dictionary<string, object>() {
                    { "id",info.Id},
                    { "name",info.Name},
                    { "des",info.Des},
                    { "pid",info.Pid}
                }) > 0;
            }
            else
            {
                string sql = "update devicetype set name=@name,des=@des,pid=@pid";
                return dataBase.IExec(sql, new Dictionary<string, object>() {
                    { "id",info.Id},
                    { "name",info.Name},
                    { "des",info.Des},
                    { "pid",info.Pid}
                }) > 0;
            }
        }

        /// <summary>
        /// 根据类型ID获取类型数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        internal DeviceTypeInfo GetDeviceTypeByID(int id)
        {
            if (id <= 0)
            {
                DeviceTypeInfo info = new DeviceTypeInfo();
                info.Name = "顶级设备类型";
                info.Id = 0;
                info.Des = "虚拟顶级分组";
                return info;
            }
            string sql = "select * from DeviceType where id = "+id;
            
            DataTable dt = dataBase.Exec(sql, null);
            if (dt != null)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    DeviceTypeInfo dev = PaseraDataRowToDeviceTypeInfo(dr);
                    return dev;
                }
            }
            return null;
        }
        /// <summary>
        /// 删除设备类型
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        internal bool DelDeviceTypeByID(int id)
        {
            string sql = "delete deviceType where id =" + id;
            return dataBase.IExec(sql, null) > 0;
        }



        internal void LoopMakeHomeData()
        {
            //设备总数量
            int deviceCount = GetDeviceCount();
            //离线数
            int offLineCount = GetOffLineDeviceCount();
            //维护数
            int maintainCount = GetMaintainDeviceCount();
            //告警数
            int waringCount = GetWarrDeviceCount();

            SaveHomeData(SumDataType.SDT_OFFLINE, offLineCount);
            SaveHomeData(SumDataType.SDT_MAINTAIN, maintainCount);
            SaveHomeData(SumDataType.SDT_NORMAL, deviceCount - offLineCount - maintainCount - waringCount);
            //查询各种告警设备个数
            SaveHomeData(SumDataType.SDT_VOLTAGE, GetWaringCount(SumDataType.SDT_VOLTAGE));
            SaveHomeData(SumDataType.SDT_CURRENT, GetWaringCount(SumDataType.SDT_CURRENT));
            SaveHomeData(SumDataType.SDT_TEMPERATURE, GetWaringCount(SumDataType.SDT_TEMPERATURE));
            SaveHomeData(SumDataType.SDT_HUMIDITY, GetWaringCount(SumDataType.SDT_HUMIDITY));

        }


        internal int GetWaringCount(SumDataType type)
        {
            string sql = "select COUNT(*) sumcount from Devices where ";
            switch(type)
            {
                case SumDataType.SDT_VOLTAGE:
                    {
                        sql += @"(not (DeviceVoltage between DeviceMinVoltage and DeviceMaxVoltage)
and datediff(MINUTE, DeviceLastUpDate, GETDATE()) <= DeviceUpDateTime
and DeviceIsMaintain = 0)";
                    }
                    break;
                case SumDataType.SDT_CURRENT:
                    {
                        sql += @"(not (DeviceElectricCurrent between DeviceMinElectricCurrent and DeviceMaxElectricCurrent)
and datediff(MINUTE, DeviceLastUpDate, GETDATE()) <= DeviceUpDateTime
and DeviceIsMaintain = 0)";
                    }
                    break;
                case SumDataType.SDT_TEMPERATURE:
                    {
                        sql += @"(not (DeviceTemperature between DeviceMinTemperature and DeviceMaxTemperature)
and datediff(MINUTE, DeviceLastUpDate, GETDATE()) <= DeviceUpDateTime
and DeviceIsMaintain = 0)";
                    }
                    break;
                case SumDataType.SDT_HUMIDITY:
                    {
                        sql += @"(not (DeviceHumidity between DeviceMinHumidity and DeviceMaxHumidity)
and datediff(MINUTE, DeviceLastUpDate, GETDATE()) <= DeviceUpDateTime
and DeviceIsMaintain = 0)";
                    }
                    break;
                default:
                    return 0;
            }
            DataTable dt = dataBase.Exec(sql, null);
            if (dt != null && dt.Rows.Count > 0)
            {
                return DBTools.GetInt(dt.Rows[0], "sumcount");
            }
            return 0;
        }


        /// <summary>
        /// 保存首页数据
        /// </summary>
        /// <param name="type"></param>
        /// <param name="value"></param>
        internal void SaveHomeData(SumDataType type,int value)
        {

            string sql = "select * from HomePageData where convert(varchar(10),SumDate,120)  = convert(varchar(10),GETDATE(),120) and SumType = " + (int)type;
            DataTable dt = dataBase.Exec(sql, null);
            if (dt != null && dt.Rows.Count > 0)
            {
                //已存在，更新它
                sql = "update HomePageData set SumValue=" + value + " where convert(varchar(10),SumDate,120)  = convert(varchar(10),GETDATE(),120) and SumType = " + (int)type;
                dataBase.IExec(sql, null);
            }
            else
            {
                //插入数据
                sql = "insert into homePageData values(" + GetMaxID("HomePageData", "id") + ",GETDATE()," + (int)type + ","+value+")";
                dataBase.IExec(sql, null);
            }
        }

        public void Dispose()
        {
            if (LoopThread != null)
            {
                LoopThread.Abort();
            }
        }
    }
}
